package lab1.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import lab1.db.DatabaseManager;
import lab1.vo.UserVO;
import lab1.vo.ValueObject;

/**
 * 
 * @author Arty
 * 
 */

public class UserDAO implements DAO {

	public void create(ValueObject vo) {
		DatabaseManager db = new DatabaseManager();
		UserVO us = (UserVO) vo;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;

		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" INSERT INTO user (user_log, user_pas, user_gr_id, user_name)");
		sb.append(" VALUES(?,?,?,?)");

		try {
			ps = con.prepareStatement(sb.toString());

			ps.setString(1, us.getLogin());
			ps.setString(2, us.getPassword());
			ps.setInt(3, (int) us.getGroupId());
			ps.setString(4, us.getName());
			ps.executeUpdate();
		

		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}
	}

	public boolean delete(long i) {
		DatabaseManager db = new DatabaseManager();
		PreparedStatement ps = null;
		Connection con = null;
		StringBuilder sb = new StringBuilder();
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" DELETE FROM user");
		sb.append(" WHERE user_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int) i);
			ps.executeUpdate();
			//con.commit();
			return true;
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}
		return false;
	}

	public boolean update(ValueObject vo) {
		if (vo == null)
			return false;
		DatabaseManager db = new DatabaseManager();
		UserVO us = (UserVO) vo;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		boolean comma = false;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" UPDATE user SET");
		if (us.getLogin() != null) {
			sb.append(" user_log=?");
			comma = true;
		}

		if (us.getPassword() != null) {
			if (comma) {
				sb.append(",");
			}
			sb.append(" user_pas=?");
			comma = true;
		}

		if(us.getName() != null){
			if (comma) {
				sb.append(",");
			}
			sb.append(" user_name=?");
		}
		sb.append(" WHERE user_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1, us.getLogin());
			ps.setString(2, us.getPassword());
			ps.setString(3, us.getName());
			ps.setInt(4, us.getId());
			ps.executeUpdate();
			// con.commit();

			return true;
		} catch (SQLException e) {
			System.out.println(e.getLocalizedMessage());
			return false;
		}
	}

	public ValueObject retrieve(ValueObject vo) {
		if (vo == null)
			return null;
		DatabaseManager db = new DatabaseManager();
		UserVO us = (UserVO) vo;
		StringBuffer sb = new StringBuffer();
		PreparedStatement ps = null;

		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" SELECT * FROM user WHERE");
		sb.append(" user_log=?");
		sb.append(" AND");
		sb.append(" user_pas=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1, us.getLogin());
			ps.setString(2, us.getPassword());
			ResultSet rs = ps.executeQuery();
			// con.commit();

			while (rs.next()) {
				us.setId(Integer.parseInt(rs.getString("user_id"), 10));
				us.setName(rs.getString("user_name"));
				us.setLogin(rs.getString("user_log"));
				us.setPassword(rs.getString("user_pas"));
				us.setGroupId(Integer.parseInt(rs.getString("user_gr_id"), 10));
				return us;
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return null;
	}// End - retrieve(UserVO us)

	public ValueObject retrieve(long i) {
		DatabaseManager db = new DatabaseManager();
		UserVO us = null;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;

		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" SELECT * FROM user WHERE");
		sb.append(" user_id=?");
		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int) i);
			ResultSet rs = ps.executeQuery();
			con.commit();

			while (rs.next()) {
				us.setId(Integer.parseInt(rs.getString("user_id"), 10));
				us.setName(rs.getString("user_name"));
				us.setLogin(rs.getString("user_log"));
				us.setPassword(rs.getString("user_pas"));
				us.setGroupId(Integer.parseInt(rs.getString("user_gr_id"), 10));
				return us;
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return null;
	}// End - retrieve(long i)

	public ArrayList<ValueObject> retrieve() {
		ArrayList<ValueObject> arr = new ArrayList<ValueObject>();
		DatabaseManager db = new DatabaseManager();
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" SELECT * FROM `user` u");

		try {
			ps = con.prepareStatement(sb.toString());
			ResultSet rs = ps.executeQuery();
			con.commit();

			while (rs.next()) {
				UserVO us = new UserVO();
				us.setId(Integer.parseInt(rs.getString("user_id"), 10));
				us.setName(rs.getString("user_name"));
				us.setLogin(rs.getString("user_log"));
				us.setPassword(rs.getString("user_pas"));
				us.setGroupId(Integer.parseInt(rs.getString("user_gr_id")));
				arr.add(us);
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return arr;
	}

}
